package dao.impl;

import dao.EvaluateDao;
import pojo.Evaluate;
import util.BaseDao;
import util.PageBean;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 用户评价类Dao接口类实现
 */
public class EvaluateDaoImpl implements EvaluateDao {
    BaseDao bd = new BaseDao();

    /**
     * 评价分页查询
     * @param pb 分页查询工具
     * @param ename 根据用户账号或者商品名称查询
     */
    @Override
    public PageBean evaluatePageList(PageBean pb, String ename) {
        List<Evaluate> list = new ArrayList<Evaluate>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = bd.getConnection();
            String s = "";
            if (null != ename && !ename.equals("")) {
                s += "and （ go_name  LIKE '%" + ename + "%'" + "or  u_account  LIKE '%" + ename + "%' )";
            }

            String sql = " SELECT * FROM (" +
                    "  SELECT ROWNUM r,a.* FROM (select ev.*,eu.u_account,g.go_name from evaluate ev,e_user eu,goods g where ev.ware_id=g.go_id and ev.consumer_id=eu.u_id)a " +
                    "  WHERE 1=1 " + s + " and ROWNUM<=?" +
                    " )WHERE r>?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, pb.getPageIndex() * pb.getPageSize());
            ps.setInt(2, pb.getPageSize() * (pb.getPageIndex() - 1));
            rs = ps.executeQuery();
            while (rs.next()) {
                Evaluate evaluate = new Evaluate();
                evaluate.setEvaluateId(rs.getInt("EVALUATE_ID"));
                evaluate.setWareId(rs.getInt("WARE_ID"));
                evaluate.setConsumerId(rs.getInt("CONSUMER_ID"));
                evaluate.setConsumerAccount(rs.getString("u_account"));
                evaluate.setWareName(rs.getString("go_name"));
                evaluate.setEvaluateScore(rs.getInt("EVALUATE_SCORE"));
                evaluate.setEvaluatePhoto(rs.getString("EVALUATE_PHOTO"));
                evaluate.setEvaluateDescribe(rs.getString("EVALUATE_DESCRIBE"));
                list.add(evaluate);
            }
            pb.setList(list);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            bd.release(rs,ps,conn);
        }
        return pb;
    }

    /**
     * 查询所有评价
     * @return list集合
     */
    @Override
    public List<Evaluate> list() {
        List<Evaluate> list = new ArrayList<Evaluate>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = bd.getConnection();
            String sql = "select * from evaluate";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Evaluate evaluate = new Evaluate();
                evaluate.setEvaluateId(rs.getInt("EVALUATE_ID"));
                evaluate.setWareId(rs.getInt("WARE_ID"));
                evaluate.setConsumerId(rs.getInt("CONSUMER_ID"));
                evaluate.setEvaluateScore(rs.getInt("EVALUATE_SCORE"));
                evaluate.setEvaluatePhoto(rs.getString("EVALUATE_PHOTO"));
                evaluate.setEvaluateDescribe(rs.getString("EVALUATE_DESCRIBE"));
                list.add(evaluate);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            bd.release(rs,ps,conn);
        }
        return list;
    }


    /**
     * 根据id查询用户评价
     * @param id 评价id
     * @return 返回结果
     */
    @Override
    public Evaluate finaEvaluate(int id) {
        Evaluate evaluate = new Evaluate();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = bd.getConnection();
            String sql = "select * from evaluate where evaluate_id=? ";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            rs = ps.executeQuery();
            while (rs.next()) {
                evaluate.setEvaluateId(rs.getInt("EVALUATE_ID"));
                evaluate.setWareId(rs.getInt("WARE_ID"));
                evaluate.setConsumerId(rs.getInt("CONSUMER_ID"));
                evaluate.setEvaluateScore(rs.getInt("EVALUATE_SCORE"));
                evaluate.setEvaluatePhoto(rs.getString("EVALUATE_PHOTO"));
                evaluate.setEvaluateDescribe(rs.getString("EVALUATE_DESCRIBE"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            bd.release(rs,ps,conn);
        }
        return evaluate;
    }

    /**
     * 新增评价
     * @param evaluate 评价对象
     * @return 返回结果
     */
    @Override
    public int InseEvaluate(Evaluate evaluate) {
        //返回影响行数
        int bo = 0;
        String sql = "insert into evaluate values(sequence_evaluate.nextval,?,?,?,?,?)";
        bo = bd.cud(sql, evaluate.getWareId(), evaluate.getConsumerId(), evaluate.getEvaluateScore(), evaluate.getEvaluatePhoto(), evaluate.getEvaluateDescribe());
        return bo;
    }

    /**
     * 修改评价
     * @param evaluate 评价对象
     * @return 返回结果
     */
    @Override
    public int updaEvaluate(Evaluate evaluate) {
        //返回影响行数
        int bo = 0;
        String sql = "update evaluate set ware_id=?,consumer_id=?,evaluate_score=?,evaluate_photo=?,evaluate_describe=? where evaluate_id=?";
        bo = bd.cud(sql, evaluate.getWareId(), evaluate.getConsumerId(), evaluate.getEvaluateScore(), evaluate.getEvaluatePhoto(), evaluate.getEvaluateDescribe(), evaluate.getEvaluateId());
        return bo;
    }


    /**
     * 删除评价
     * @param id 评价id
     * @return 返回结果
     */
    @Override
    public int deleEvaluate(int id) {
        //返回影响行数
        int bo = 0;
        String sql = "delete from evaluate where evaluate_id=?";
        bo = bd.cud(sql, id);
        return bo;
    }

    /**
     * 根据组合查询统计评价总页数
     * @param ename 用户账号或商品名称
     * @return 返回结果
     */
    @Override
    public int evaluateCount(String ename) {
        //统计评价总页数
        int count = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = bd.getConnection();
            String s = "";
            if (null != ename && !ename.equals("")) {
                s += "and  go_name  LIKE '%" + ename + "%'" + "and  u_account  LIKE '%" + ename + "%'";
            }

            String sql = "SELECT count(*) FROM (select ev.evaluate_id,eu.u_account,g.go_name from evaluate ev,e_user eu,goods g where ev.ware_id=g.go_id and ev.consumer_id=eu.u_id) where 1=1 " + s;
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }
}
